<?php   if (!defined('BASEPATH')) exit('No direct script access allowed');


class Rimesse_model extends CI_Model {
	
    function __construct()
    {
        parent::__construct();
    }

    function delete($where)
	{
        $this->db->where($where);
		$this->db->delete('rimesse');
        $true = $this->db->affected_rows() > 0; 
		return $true;
	}
    
    function save($data)
    {
        
   		if(!isset($data['id']) || !$data['id'])
        {
            $query = $this->db->query("SELECT max(id) as id FROM rimesse");
            $row = $query->row_array();
            $data['id'] = $row['id']+1;
			$data['username'] = $this->user->username;
            
            $this->db->insert('rimesse', $data);
        }
        else
        {
            $this->db->where('id', $data['id']);
			
            $this->db->update('rimesse', $data);
        }
		return $data['id'];

    }

	
    function get_columns()
	{
		$sql = "SELECT column_name from information_schema.columns where
table_name='" . $this->db->dbprefix('rimesse') . "'";
		$query = $this->db->query($sql);
		
		return $query->result_array();
	}
	
    function get_missioni()
    {
        $sql = "SELECT id, nome FROM missioni WHERE attivo = 1 ORDER BY nome";
        $query = $this->db->query($sql);
        
        return $query->result_array();
   
    }

    
    function get_anni()
    {
        $sql = "SELECT distinct extract(year from data) as anno FROM offerte ORDER BY extract(year from data)";
        $query = $this->db->query($sql);
        
        return $query->result_array();
    }
    
    
	function get_total($params = array())
	{
		$default_params = array
		(
			'where' => null
		);
        
		foreach ($default_params as $key => $value)
		{
			$params[$key] = (isset($params[$key]))? $params[$key]: $default_params[$key];
		}
        
		$this->db->select('count(o.id) as nb');
		$this->db->from('offerte as o');
		if(!is_null($params['where']))
		{
			$this->db->where( $params['where']);
		}
		$this->db->join('assistiti as a', 'a.id = o.id_assistito', 'left');
		$this->db->join('sostenitori as s', 's.id = o.id_sostenitore', 'left');
		$this->db->join('missioni as m', 'm.id = o.id_missione', 'left');
		$query = $this->db->get();
		$row = $query->row();
		return $row->nb;
	}
		
	function get_offerte_mensili($anno, $id_categoria = false)
	{
		$where = "WHERE (extract(year from data)=$anno) ";
		if($id_categoria) $where .= " AND id_categoria = " . $id_categoria;

		$sql = "select extract(month from data) as mese, sum(valore) as lordo,sum(trattenuta)as trattenute ,sum(netto) as netto from offerte " . $where . " group by extract(month from data) ORDER BY mese";
		
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	function get_list($params = array())
	{
		$default_params = array
		(
			'select' => 'r.*',
			'order_by' => 'r.nome',
			'limit' => 100,
			'where' => null,
			'start' => 0,
		);
		
		foreach ($default_params as $key => $value)
		{
			$params[$key] = (isset($params[$key]))? $params[$key]: $default_params[$key];
		}
	
		$this->db->select($params['select']);
		$this->db->from('rimesse as r');
		
		//$this->db->join('adozioni as ad', '(ad.id_assistito = o.id_assistito AND ad.id_sostenitore=o.id_sostenitore)', 'left');
		
		
		if(!is_null($params['where']))
		{
			$this->db->where($params['where']);
		}
		$this->db->order_by($params['order_by']);
		
		$this->db->limit($params['limit'], $params['start']);
		
		$query = $this->db->get();
		return $query->result_array();
	}



	function get_rimesse_trimestre($anno =  null, $trimestre = null)
	{
		$str_trimestre = array(1 => '1,2,3', 2 => '4,5,6', 3 => '7,8,9', 4 => '10,11,12');
		
		$sql = "select r.nome, r.conto, sum(o.valore) as lordo, sum(o.trattenuta) as trattenute, sum(o.netto) as totale 
		from rimesse as r
		left join missioni as m on m.id_rimesse = r.id
		left join offerte as o on o.id_missione = m.id
		where extract(month from data) in (". $str_trimestre[$trimestre] .") and extract(year from data) = " . $anno . " 
		and o.trashed = 0
		group by r.nome, r.conto
		order by r.nome";

		$query = $this->db->query($sql);
		
		if($query->num_rows() > 0)
		{
			return $query->result_array();
		}
		else
		{
			return false;
		}


	}
	
	
}